package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.City;
import com.wanmait.hotelManage.vo.Pager;
import com.wanmait.hotelManage.vo.Province;
import com.wanmait.hotelManage.vo.Userinfo;
import com.wanmait.hotelManage.vo.Vip;

public class UserinfoDAO {
	private UserinfoDAO() {
	};

	private static UserinfoDAO userinfodao = new UserinfoDAO();

	public static UserinfoDAO getUserinfodao() {
		return userinfodao;
	}
	public void updpassword( String newpassword, int id) {
		String sql="UPDATE userinfo SET PASSWORD=? WHERE id=?";
		DBHelper.getDBHelper().executeUpdate(sql,newpassword,id);
	}
	public void upduserinfo(String username,String userTelePhone,int userCityId , int userId) {
		
		  StringBuilder sb=new StringBuilder();
		  sb.append(" UPDATE userinfo SET username =? , telephone_number=?  ,city_id=? ");
		  sb.append(" WHERE id=? ");
		  DBHelper.getDBHelper().executeUpdate(sb.toString(),username,userTelePhone, userCityId,userId);
		
	}
  public void upduserinfo(String username,String userTelePhone, String pic,int userCityId , int userId) {
	  StringBuilder sb=new StringBuilder();
	  sb.append(" UPDATE userinfo SET username =? , telephone_number=? , pic=? ,city_id=? ");
	  sb.append(" WHERE id=? ");
	  DBHelper.getDBHelper().executeUpdate(sb.toString(),username,userTelePhone, pic,userCityId,userId);
  }
   public int findCity(String cityName) {
	   Connection conn=null;
	   PreparedStatement ps=null;
	   ResultSet rs=null;
	   City city=null;
	   try {
		   city=new City();
		String sql="SELECT * FROM city WHERE NAME=?";
		   conn=DBHelper.getDBHelper().getConn();
		   ps=conn.prepareStatement(sql);
		   ps.setString(1, cityName);
		   rs=ps.executeQuery();
		   rs.next();
		   city.setId(rs.getInt("id"));
	} catch (Exception e) {
		e.printStackTrace();
	}
	   return city.getId();
   }
	public Userinfo login(String username, String password) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Userinfo userinfo = null;
		StringBuilder sb = new StringBuilder();
		sb.append(" select userinfo.id,username,password,pic,gender,telephone_number, ").append(
				" userinfo.enable, input_time,city_id,city.id,province.id,province.name,city.name,city.id from userinfo,city,province  ")
				.append(" where username=? and PASSWORD=? and userinfo.city_id=city.id ")
				.append(" AND city.province_id=province.id ");
		String sql = sb.toString();
		try {
			conn = DBHelper.getDBHelper().getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			rs = ps.executeQuery();
			rs.next();
			City city = new City();
			Province province = new Province();
			province.setId(rs.getInt("province.id"));
			province.setName(rs.getString("province.name"));
			city.setId(rs.getInt("city.id"));
			city.setName(rs.getString("city.name"));
			city.setProvince(province);
			userinfo = new Userinfo();
			// userinfo.setBirthday(rs.getTimestamp("birthday"));
			userinfo.setCity(city);
			userinfo.setEnable(rs.getBoolean("userinfo.enable"));
			userinfo.setGender(rs.getBoolean("gender"));
			userinfo.setId(rs.getInt("userinfo.id"));
			userinfo.setInputTime(rs.getTimestamp("input_time"));
			userinfo.setPassword(rs.getString("password"));
			userinfo.setPic(rs.getString("pic"));
			userinfo.setTelephoneNumber(rs.getString("telephone_number"));
			userinfo.setUsername(rs.getString("username"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return userinfo;
	}

	public Userinfo findByUserinfoUsernameAndpassword(String username,String password) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		 Userinfo userinfo=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select vip_start_time,vip_end_time,vip.id,vip.price,vip.discount,vip.input_time,vip.title,userinfo.id,username,password,pic,gender,telephone_number, ")
		.append(" userinfo.enable, userinfo.input_time,city_id,city.id,province.id,province.name,city.name,city.id from userinfo,city,province,vip  ")
		.append(" where username=? and PASSWORD=? and userinfo.city_id=city.id ")
		.append(" AND city.province_id=province.id ");
		sb.append(" and userinfo.vip_id=vip.id ");
		String sql=sb.toString();

		try {
			conn = DBHelper.getDBHelper().getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			rs = ps.executeQuery();
			rs.next();
			City city = new City();
			Province province = new Province();
			province.setId(rs.getInt("province.id"));
			province.setName(rs.getString("province.name"));
			city.setId(rs.getInt("city.id"));
			city.setName(rs.getString("city.name"));
			city.setProvince(province);
			userinfo = new Userinfo();
			// userinfo.setBirthday(rs.getTimestamp("birthday"));
			userinfo.setCity(city);
			userinfo.setEnable(rs.getBoolean("userinfo.enable"));
			userinfo.setGender(rs.getBoolean("gender"));
			userinfo.setId(rs.getInt("userinfo.id"));
			userinfo.setInputTime(rs.getTimestamp("userinfo.input_time"));
			userinfo.setPassword(rs.getString("password"));
			userinfo.setPic(rs.getString("pic"));
			userinfo.setTelephoneNumber(rs.getString("telephone_number"));
			userinfo.setUsername(rs.getString("username"));
			
			Vip vip=new Vip();
			vip.setDiscount(rs.getDouble("vip.discount"));
			vip.setEnable(true);
			vip.setId(rs.getInt("vip.id"));
			vip.setInputTime(rs.getTimestamp("vip.input_time"));
			vip.setPrice(rs.getDouble("vip.price"));
			vip.setTitle(rs.getString("vip.title"));
			
			userinfo.setVip(vip);
			userinfo.setVipStartTime(rs.getTimestamp("vip_start_time"));
			userinfo.setVipEndTime(rs.getTimestamp("vip_end_time"));
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return userinfo;
	}
	public Userinfo FinfUserinfo(int id) {
		
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		 Userinfo userinfo=null;
//	     String sql=" select * from userinfo,city,province where userinfo.id=?";
		 
		 StringBuilder sb=new StringBuilder();
			sb.append(" select vip_start_time,vip_end_time,vip.id,vip.price,vip.discount,vip.input_time,vip.title,userinfo.id,username,password,pic,gender,telephone_number, ")
			.append(" userinfo.enable, userinfo.input_time,city_id,city.id,province.id,province.name,city.name,city.id from userinfo,city,province,vip  ")
			.append(" where userinfo.id=? and userinfo.city_id=city.id ")
			.append(" AND city.province_id=province.id ");
			sb.append(" and userinfo.vip_id=vip.id ");
		 
		try {
			
			conn = DBHelper.getDBHelper().getConn();
			ps = conn.prepareStatement(sb.toString());
		    ps.setInt(1, id);
			rs = ps.executeQuery();
//			rs.next();
//			City city = new City();
//			Province province = new Province();
//			province.setId(rs.getInt("province.id"));
//			province.setName(rs.getString("province.name"));
//			city.setId(rs.getInt("city.id"));
//			city.setName(rs.getString("city.name"));
//			city.setProvince(province);
//			userinfo = new Userinfo();
//			// userinfo.setBirthday(rs.getTimestamp("birthday"));
//			userinfo.setCity(city);
//			userinfo.setEnable(rs.getBoolean("userinfo.enable"));
//			userinfo.setGender(rs.getBoolean("gender"));
//			userinfo.setId(rs.getInt("userinfo.id"));
//			userinfo.setInputTime(rs.getTimestamp("input_time"));
//			userinfo.setPassword(rs.getString("password"));
//			userinfo.setPic(rs.getString("pic"));
//			userinfo.setTelephoneNumber(rs.getString("telephone_number"));
//			userinfo.setUsername(rs.getString("username"));
//			
			
			
			
			
			rs.next();
			City city = new City();
			Province province = new Province();
			province.setId(rs.getInt("province.id"));
			province.setName(rs.getString("province.name"));
			city.setId(rs.getInt("city.id"));
			city.setName(rs.getString("city.name"));
			city.setProvince(province);
			userinfo = new Userinfo();
			// userinfo.setBirthday(rs.getTimestamp("birthday"));
			userinfo.setCity(city);
			userinfo.setEnable(rs.getBoolean("userinfo.enable"));
			userinfo.setGender(rs.getBoolean("gender"));
			userinfo.setId(rs.getInt("userinfo.id"));
			userinfo.setInputTime(rs.getTimestamp("userinfo.input_time"));
			userinfo.setPassword(rs.getString("password"));
			userinfo.setPic(rs.getString("pic"));
			userinfo.setTelephoneNumber(rs.getString("telephone_number"));
			userinfo.setUsername(rs.getString("username"));
			
			Vip vip=new Vip();
			vip.setDiscount(rs.getDouble("vip.discount"));
			vip.setEnable(true);
			vip.setId(rs.getInt("vip.id"));
			vip.setInputTime(rs.getTimestamp("vip.input_time"));
			vip.setPrice(rs.getDouble("vip.price"));
			vip.setTitle(rs.getString("vip.title"));
			
			userinfo.setVip(vip);
			userinfo.setVipStartTime(rs.getTimestamp("vip_start_time"));
			userinfo.setVipEndTime(rs.getTimestamp("vip_end_time"));
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return userinfo;
	}
  public List<Province> getProvince() {
	  List<Province> provinces=null;
	  Connection conn=null;
	  PreparedStatement ps=null;
	  ResultSet rs=null;
	  String sql=" SELECT * FROM province  ";
	  try {
		  provinces=new ArrayList();
		  conn=DBHelper.getDBHelper().getConn();
		  ps=conn.prepareStatement(sql);
		  rs=ps.executeQuery();
		  while(rs.next()) {
			  Province province=new Province();
			  province.setId(rs.getInt("province.id"));
			  province.setName(rs.getString("province.name"));
			provinces.add(province);
		  }
		
	} catch (Exception e) {
		e.printStackTrace();
	}
	  return provinces;
  }
  public List<City> FindCityandPrivace( int id){
	  List< City> citys=null;
	  Connection conn=null;
	  PreparedStatement ps=null;
	  ResultSet rs=null;
	  String sql=" SELECT *FROM city WHERE city.province_id=? ";
	  try {
		  conn=DBHelper.getDBHelper().getConn();
		  ps=conn.prepareStatement(sql);
		  ps.setInt(1, id);
		  rs=ps.executeQuery();
		  citys= new ArrayList();
		  while(rs.next()) {
			
			City city=new City();
			city.setId(rs.getInt("id"));
			city.setName(rs.getString("name"));
			
			citys.add(city);
		  }
		
	} catch (Exception e) {
		e.printStackTrace();
	}
	  finally {
		  DBHelper.getDBHelper().close(rs, ps, conn);
	  }
	  return citys;
  }
  
  public List<City> FindCity(){
	  List< City> citys=null;
	  Connection conn=null;
	  PreparedStatement ps=null;
	  ResultSet rs=null;
	  String sql="SELECT * FROM city,province WHERE province.id=city.province_id ";
	  try {
		  conn=DBHelper.getDBHelper().getConn();
		  ps=conn.prepareStatement(sql);
		  rs=ps.executeQuery();
		  while(rs.next()) {
			  citys=new ArrayList();
			  Province province=new Province();
			  province.setId(rs.getInt("province.id"));
			  province.setName(rs.getString("province.name"));
			City city=new City();
			city.setId(rs.getInt("id"));
			city.setName(rs.getString("name"));
			city.setProvince(province);
			citys.add(city);
		  }
		
	} catch (Exception e) {
		e.printStackTrace();
	}
	  return citys;
  }
  public City ByIdFindCity( int Id){
	   City city=null;
	  Connection conn=null;
	  PreparedStatement ps=null;
	  ResultSet rs=null;
	  String sql=" SELECT * FROM city,userinfo,province WHERE userinfo.city_id=city.id AND userinfo.id=? AND province.id=city.province_id ";
	  try {
		  conn=DBHelper.getDBHelper().getConn();
		  ps=conn.prepareStatement(sql);
		  ps.setInt(1, Id);
		  rs=ps.executeQuery();
		  while(rs.next()) {
			  Province province=new Province();
			  province.setId(rs.getInt("province.id"));
			  province.setName(rs.getString("province.name"));
			 city=new City();
			city.setId(rs.getInt("id"));
			city.setName(rs.getString("name"));
			city.setProvince(province);
			
		  }
		
	} catch (Exception e) {
		e.printStackTrace();
	}
	  finally {
		  DBHelper.getDBHelper().close(rs, ps, conn);
	  }
	  return city;
  }
	// 添加
	public void insert(Userinfo userinfo) {
		String sql = "insert into userinfo(username,password) values(?,?)";
		DBHelper.getDBHelper().executeUpdate(sql, userinfo.getUsername(),userinfo.getPassword());
	}

	// 注册 验证用户名唯一
	public boolean exsitUsername(String username) {
		boolean exsit = false;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select id from userinfo where username=?";
		try {
			conn = DBHelper.getDBHelper().getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			if (rs.next()) {
				exsit = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return exsit;
	}
	/**
	 * 用户管理的初始化
	 * @param pager 
	 * @return
	 */
	public List<Userinfo>findAll(Pager pager){
		List<Userinfo>userinfos=new ArrayList<Userinfo>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT userinfo.id,userinfo.username,userinfo.pic,userinfo.vip_id,userinfo.vip_start_time,userinfo.vip_end_time,"
				+ "userinfo.telephone_number,vip.id,vip.title,userinfo.input_time,userinfo.city_id,"
				+ "city.name FROM userinfo,city,vip WHERE userinfo.vip_id=vip.id and userinfo.enable=1 AND userinfo.city_id=city.id limit ?,?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			Userinfo userinfo=null;
			Vip vip=null;
			City c=null;
			while(rs.next()) {
				userinfo=new Userinfo();
				userinfo.setId(rs.getInt("userinfo.id"));
				userinfo.setUsername(rs.getString("userinfo.username"));
				userinfo.setPic(rs.getString("userinfo.pic"));
				userinfo.setTelephoneNumber(rs.getString("userinfo.telephone_number"));
				userinfo.setInputTime(rs.getTimestamp("userinfo.input_time"));
				c=new City();
				c.setId(rs.getInt("userinfo.city_id"));
				c.setName(rs.getString("city.name"));
				userinfo.setCity(c);
				vip=new Vip();
				vip.setId(rs.getInt("vip.id"));
				vip.setTitle(rs.getString("vip.title"));
				userinfo.setVip(vip);
				userinfo.setVipStartTime(rs.getTimestamp("userinfo.vip_start_time"));
				userinfo.setVipEndTime(rs.getTimestamp("userinfo.vip_end_time"));
				userinfos.add(userinfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return userinfos;
	}
	public int getrows(){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) c from userinfo where enable=1 ");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			Integer count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
//	public static void main(String[] args) {
//		List<Userinfo>user=UserinfoDAO.getUserinfodao().findAll();
//		for (Userinfo userinfo : user) {
//			System.out.println(userinfo.getCity().getName());
//		}
//	}
	//根据用户ID查找打折力度
	public Double findDiscountByUserinfoId(Integer id) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="  SELECT discount  " + 
				"  FROM userinfo,vip " + 
				"  WHERE userinfo.vip_id=vip.id " + 
				"  AND userinfo.id=?";
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			System.out.println(id);
			System.out.println(ps);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				return rs.getDouble("discount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return null;
	}
	public int findvip(Integer id) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select vip_id from userinfo where id=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				int a=rs.getInt("vip_id");
				return a;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
//	public static void main(String[] args) {
//		int a=UserinfoDAO.getUserinfodao().findvip(1);
//		System.out.println(a);
//	}
}
